import json
import telnetlib
import time

import pymysql
def savesql(sql):
    host = '192.168.2.105'
    user = 'root'
    pwd = 'root'
    table = 'heanny'
    db = pymysql.connect(host,user,pwd,table)
    db.set_charset('utf8')
    # db.set_charset('utf-8')
    cursor = db.cursor()
    cursor.execute(sql)
    # data = cursor.fetchone()#读取第一行信息
    data = cursor.fetchall()#读取全部信息
    print(data)
    db.commit()
    db.close()
    return data

def checkProxt(ip,port,timeout=5):
    try:
        Tel =telnetlib.Telnet(ip, port=port, timeout=timeout)
    except:
        return False
    else:
        Tel.close()
        print(ip,port)
        return True


def saveXicidaili(Ps):
    '''保存西刺代理ip池'''
    for p in Ps:
        p['isHttps'] = 1 if p['isHttps'] else 0
        p['anonymous'] = 1 if p['anonymous'] else 0
        sql = "insert IGNORE into `proxies`(ip,port,isHttps,lifeTime,verifyTime,anonymous) values ('{ip}','{port}','{isHttps}','{lifeTime}','{verifyTime}','{anonymous}')".format(**p)
        print(sql)
        savesql(sql)
def saveScylla(Ps):
    print(len(Ps))
    '''保存scylla里面的代理池'''
    for p in Ps:
        print(p)
        if p['is_valid']:
            p['anonymous']= 1 if p['is_anonymous'] else 0
            p['isHttps'] = 1 if p['is_https'] else 0
            p['verifyTime'] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(p['updated_at']))
            p['lifeTime'] ='-'
            sql = "insert IGNORE into `proxies`(ip,port,isHttps,lifeTime,verifyTime,anonymous) values ('{ip}','{port}','{isHttps}','{lifeTime}','{verifyTime}','{anonymous}')".format(
                **p)
            print(sql)
            savesql(sql)
    return
def saveKuaidaili(Ps):
    for p in Ps:
        print(p)
        # p['verifyTime'] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(p['updated_at']))
        p['lifeTime'] = '-'
        sql = "insert IGNORE into `proxies`(ip,port,isHttps,lifeTime,verifyTime,anonymous) values ('{ip}','{port}','{isHttps}','{lifeTime}','{verifyTime}','{anonymous}')".format(
            **p)
        print(sql)
        savesql(sql)


def main():
    Proxies = savesql("select ip,port,isHttps from proxies")
    NewProxy=[]
    print(len(Proxies))
    for p in Proxies:
        if checkProxt(p[0],p[1]):
            NewProxy.append(p)
    with open('proxies.json','w+') as f:
        f.write(json.dumps(NewProxy))
if __name__ == '__main__':
    # Ps=[{'id': 626, 'ip': '186.192.28.138', 'port': 8080, 'is_valid': True, 'created_at': 1572011447, 'updated_at': 1572014923, 'latency': 375.0, 'stability': 0.4, 'is_anonymous': False, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 633, 'ip': '131.161.238.94', 'port': 8090, 'is_valid': True, 'created_at': 1572011452, 'updated_at': 1572014920, 'latency': 425.0, 'stability': 0.4, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 624, 'ip': '182.52.74.76', 'port': 34084, 'is_valid': True, 'created_at': 1572011446, 'updated_at': 1572014919, 'latency': 257.0, 'stability': 0.3, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '13.754,100.5014', 'organization': 'TOT Public Company Limited', 'region': 'Bangkok', 'country': 'TH', 'city': 'Bangkok'}, {'id': 635, 'ip': '93.99.68.177', 'port': 44017, 'is_valid': True, 'created_at': 1572011452, 'updated_at': 1572014917, 'latency': 305.0, 'stability': 0.6, 'is_anonymous': True, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '48.7333,16.9667', 'organization': 'Liberty Global B.V.', 'region': 'South Moravian', 'country': 'CZ', 'city': 'Lanzhot'}, {'id': 618, 'ip': '115.85.83.197', 'port': 8080, 'is_valid': True, 'created_at': 1572011443, 'updated_at': 1572014914, 'latency': 258.0, 'stability': 0.5, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 614, 'ip': '95.134.195.78', 'port': 48935, 'is_valid': True, 'created_at': 1572011437, 'updated_at': 1572014914, 'latency': 307.0, 'stability': 0.4, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '49.4572,35.1298', 'organization': 'PJSC Ukrtelecom', 'region': "Poltavs'ka Oblast'", 'country': 'UA', 'city': 'Karlivka'}, {'id': 611, 'ip': '138.97.12.150', 'port': 49649, 'is_valid': True, 'created_at': 1572011434, 'updated_at': 1572014908, 'latency': 370.0, 'stability': 0.1, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '-22.7882,-43.327', 'organization': 'RedeBr Telecom', 'region': 'Rio de Janeiro', 'country': 'BR', 'city': 'Duque de Caxias'}, {'id': 613, 'ip': '103.223.10.14', 'port': 8080, 'is_valid': True, 'created_at': 1572011436, 'updated_at': 1572014907, 'latency': 337.0, 'stability': 0.9, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 586, 'ip': '202.166.207.109', 'port': 8080, 'is_valid': True, 'created_at': 1572011417, 'updated_at': 1572014900, 'latency': 475.0, 'stability': 0.2, 'is_anonymous': False, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 603, 'ip': '179.27.83.222', 'port': 51517, 'is_valid': True, 'created_at': 1572011429, 'updated_at': 1572014899, 'latency': 370.0, 'stability': 0.1, 'is_anonymous': True, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '-34.8576,-56.1702', 'organization': 'Administracion Nacional de Telecomunicaciones', 'region': 'Departamento de Montevideo', 'country': 'UY', 'city': 'Montevideo'}, {'id': 595, 'ip': '81.26.147.42', 'port': 8080, 'is_valid': True, 'created_at': 1572011423, 'updated_at': 1572014899, 'latency': 242.0, 'stability': 0.1, 'is_anonymous': False, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 592, 'ip': '195.77.80.154', 'port': 42402, 'is_valid': True, 'created_at': 1572011421, 'updated_at': 1572014896, 'latency': 375.0, 'stability': 0.1, 'is_anonymous': True, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '38.6447,0.0445', 'organization': 'Telefonica De Espana', 'region': 'Valencia', 'country': 'ES', 'city': 'Calpe'}, {'id': 599, 'ip': '202.7.54.35', 'port': 8080, 'is_valid': True, 'created_at': 1572011428, 'updated_at': 1572014893, 'latency': 212.0, 'stability': 0.3, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '11.5583,104.9121', 'organization': 'CityLink Corporation, LTD', 'region': 'Phnom Penh', 'country': 'KH', 'city': 'Phnom Penh'}, {'id': 582, 'ip': '154.236.162.50', 'port': 8080, 'is_valid': True, 'created_at': 1572011416, 'updated_at': 1572014892, 'latency': 332.0, 'stability': 0.4, 'is_anonymous': True, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 584, 'ip': '180.150.53.35', 'port': 8080, 'is_valid': True, 'created_at': 1572011416, 'updated_at': 1572014891, 'latency': 287.0, 'stability': 0.2, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 602, 'ip': '46.225.128.250', 'port': 8080, 'is_valid': True, 'created_at': 1572011429, 'updated_at': 1572014891, 'latency': 446.0, 'stability': 0.3, 'is_anonymous': True, 'is_https': False, 'attempts': 1, 'https_attempts': 0, 'location': '39.9288,116.3889', 'organization': 'China Unicom Beijing Province Network', 'region': 'Beijing', 'country': 'CN', 'city': 'Beijing'}, {'id': 572, 'ip': '179.162.153.29', 'port': 8080, 'is_valid': True, 'created_at': 1572011278, 'updated_at': 1572014885, 'latency': 342.0, 'stability': 1.0, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '-5.1063,-42.8019', 'organization': 'TELEFÔNICA BRASIL S.A', 'region': 'Piaui', 'country': 'BR', 'city': 'Teresina'}, {'id': 807, 'ip': '103.57.36.223', 'port': 8080, 'is_valid': True, 'created_at': 1572011575, 'updated_at': 1572013242, 'latency': 306.0, 'stability': 0.4, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '-6.9217,107.6071', 'organization': 'PT. Cemerlang Multimedia', 'region': 'West Java', 'country': 'ID', 'city': 'Bandung'}, {'id': 796, 'ip': '188.163.170.130', 'port': 41209, 'is_valid': True, 'created_at': 1572011568, 'updated_at': 1572013239, 'latency': 458.0, 'stability': 0.6, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '48.4602,35.0328', 'organization': 'Kyivstar PJSC', 'region': 'Dnipropetrovsk', 'country': 'UA', 'city': 'Dnipro'}, {'id': 812, 'ip': '176.226.240.215', 'port': 8080, 'is_valid': True, 'created_at': 1572011577, 'updated_at': 1572013238, 'latency': 115.0, 'stability': 1.0, 'is_anonymous': True, 'is_https': True, 'attempts': 1, 'https_attempts': 1, 'location': '55.158,61.4312', 'organization': 'Intersvyaz-2 JSC', 'region': 'Chelyabinsk', 'country': 'RU', 'city': 'Chelyabinsk'}]
    # print(len(Ps))
    # saveScylla(Ps)

    main()

        # checkProxt(p['ip'],p['port'])